﻿use tp
go
alter procedure GetFiasObject
	@term sysname=null, -- part of name
	@count int=null, -- count of result rows
	@level int=null,
	@aoguid sysname=null,
	@parentGuid sysname = null,
	@regCode int = null
as	

;with cte as ( 
	select a.AOGUID,a.POSTALCODE,a.PARENTGUID, a.REGIONCODE,a.SHORTNAME,isnull(a.OFFNAME,FORMALNAME) as OffName from fias.dbo.addrobj a
	where (@regCode is not null and a.REGIONCODE = @regCode or @regCode is null) 
		  and ((@level is null or AOLEVEL = @level )
	      and (@term is null or @term is not null and FORMALNAME like '%'+@term+'%'))
	)
	select top(@count) 
		c.* 
		,a.FORMALNAME +', ' +s.SOCRNAME as ParentName
	from cte c
	  left join fias.dbo.addrobj a on c.PARENTGUID = a.AOGUID
	  left join fias.dbo.socrbase s on a.AOLEVEL = s.LEVEL and a.SHORTNAME = s.SCNAME
	where (@aoguid is not null and c.AOGUID = @aoguid or @aoguid is null) and
		  (@parentGuid is not null and c.PARENTGUID = @parentGuid or @parentGuid is null) 

	order by c.OffName
go

exec GetFiasObject 
	 @term = ''
	,@count = 200
	,@level = 7
	--,@aoguid =  'b6ba5716-eb48-401b-8443-b197c9578734'
	,@parentGuid ='c0ac6992-e48e-4648-b176-f56e7bcda57b'
	,@regCode = 33
go

select distinct shortname from fias.dbo.addrobj where AOLEVEL=3

/*
select * from fias.dbo.addrobj where AOGUID = 'c0ac6992-e48e-4648-b176-f56e7bcda57b'
select * from fias.dbo.addrobj where PARENTGUID = 'c0ac6992-e48e-4648-b176-f56e7bcda57b'

*/

select * from fias.dbo.addrobj a where a.AOLEVEL = 6